package com.abc.recycling.repository;

import com.abc.recycling.model.Order;
import org.springframework.stereotype.Repository;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
@SuppressWarnings("unchecked")
public class OrderRepositoryCustomImpl implements OrderRepositoryCustom {

    @PersistenceContext
    private EntityManager em;

    @Override
    public List<Order> findByUserIdWithPaging(Long userId, int offset, int size) {
        String jpql = "SELECT o FROM Order o WHERE o.userId = :userId ORDER BY o.createTime DESC";
        return em.createQuery(jpql, Order.class)
                .setParameter("userId", userId)
                .setFirstResult(offset)
                .setMaxResults(size)
                .getResultList();
    }

    @Override
    public List<Map<String, Object>> getOrderTrendData() {
        String sql = "SELECT DATE_FORMAT(o.create_time, '%Y-%m') as month, COUNT(*) as count " +
                    "FROM recycling_order o " +
                    "GROUP BY DATE_FORMAT(o.create_time, '%Y-%m') " +
                    "ORDER BY month DESC";
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    @Override
    public List<Map<String, Object>> getItemDistributionData() {
        String sql = "SELECT o.item_type as type, COUNT(*) as count " +
                    "FROM recycling_order o " +
                    "WHERE o.status = 2 " +
                    "GROUP BY o.item_type";
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    @Override
    public long countOrdersByDate(LocalDateTime date) {
        String sql = "SELECT COUNT(*) FROM recycling_order WHERE DATE(create_time) = DATE(:date)";
        Query query = em.createNativeQuery(sql)
                .setParameter("date", date);
        return ((Number) query.getSingleResult()).longValue();
    }

    @Override
    public List<Map<String, Object>> getOrderTrendByCollector(Long collectorId, LocalDateTime startDate, LocalDateTime endDate) {
        String sql = "SELECT DATE_FORMAT(create_time, '%Y-%m-%d') as date, " +
                    "COUNT(*) as count " +
                    "FROM recycling_order " +
                    "WHERE collector_id = :collectorId " +
                    "AND create_time BETWEEN :startDate AND :endDate " +
                    "GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d') " +
                    "ORDER BY date";
                    
        Query query = em.createNativeQuery(sql)
                .setParameter("collectorId", collectorId)
                .setParameter("startDate", startDate)
                .setParameter("endDate", endDate);
                
        List<Object[]> results = query.getResultList();
        List<Map<String, Object>> trendData = new ArrayList<>();
        
        // 创建日期到数量的映射
        Map<String, Long> dateCountMap = new HashMap<>();
        for (Object[] result : results) {
            String date = (String) result[0];
            Long count = ((Number) result[1]).longValue();
            dateCountMap.put(date, count);
        }
        
        // 生成连续的日期范围
        LocalDateTime currentDate = startDate;
        while (!currentDate.isAfter(endDate)) {
            String dateStr = currentDate.format(java.time.format.DateTimeFormatter.ofPattern("yyyy-MM-dd"));
            Map<String, Object> dayData = new HashMap<>();
            dayData.put("date", dateStr);
            dayData.put("count", dateCountMap.getOrDefault(dateStr, 0L));
            trendData.add(dayData);
            currentDate = currentDate.plusDays(1);
        }
        
        return trendData;
    }

    @Override
    public Map<String, Long> getItemDistributionByCollector(Long collectorId) {
        String sql = "SELECT o.item_type as type, COUNT(*) as count " +
                    "FROM recycling_order o " +
                    "WHERE o.collector_id = :collectorId " +
                    "AND o.status = 2 " +
                    "GROUP BY o.item_type";
        Query query = em.createNativeQuery(sql)
                .setParameter("collectorId", collectorId);
        List<Object[]> results = query.getResultList();
        
        Map<String, Long> distribution = new HashMap<>();
        for (Object[] result : results) {
            distribution.put((String) result[0], ((Number) result[1]).longValue());
        }
        return distribution;
    }

    @Override
    public Map<String, Long> getCollectorOrderStats(Long collectorId) {
        Map<String, Long> stats = new HashMap<>();
        
        String statusSql = "SELECT status, COUNT(*) " +
                          "FROM recycling_order " +
                          "WHERE collector_id = :collectorId " +
                          "GROUP BY status";
        Query statusQuery = em.createNativeQuery(statusSql)
                .setParameter("collectorId", collectorId);
        List<Object[]> statusResults = statusQuery.getResultList();
        
        for (Object[] result : statusResults) {
            stats.put(String.valueOf(result[0]), ((Number) result[1]).longValue());
        }
        
        return stats;
    }

    @Override
    public Map<String, Object> getCollectorEvaluationStats(Long collectorId) {
        Map<String, Object> stats = new HashMap<>();
        
        // 获取平均评分
        String avgSql = "SELECT COALESCE(AVG(rating), 0) FROM recycling_order " +
                       "WHERE collector_id = :collectorId AND rating IS NOT NULL";
        Query avgQuery = em.createNativeQuery(avgSql)
                .setParameter("collectorId", collectorId);
        stats.put("averageRating", ((Number) avgQuery.getSingleResult()).doubleValue());
        
        // 获取评分分布
        String distSql = "SELECT rating, COUNT(*) FROM recycling_order " +
                        "WHERE collector_id = :collectorId AND rating IS NOT NULL " +
                        "GROUP BY rating ORDER BY rating";
        Query distQuery = em.createNativeQuery(distSql)
                .setParameter("collectorId", collectorId);
        List<Object[]> distResults = distQuery.getResultList();
        
        Map<Integer, Long> distribution = new HashMap<>();
        for (Object[] result : distResults) {
            distribution.put(((Number) result[0]).intValue(), ((Number) result[1]).longValue());
        }
        stats.put("ratingDistribution", distribution);
        
        return stats;
    }
} 